package com.itheima.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.domain.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface AdminDao extends BaseMapper<Admin> {
    @Select("SELECT * FROM user")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "realName", column = "real_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "registerTime", column = "register_time")
    })
    List<User> findAllUsers();

    @Select("SELECT * FROM product")
    List<Product> findAllProducts();

    @Select("SELECT * FROM category")
    List<Category> findAllCategories();

    @Select("SELECT * FROM orders")
    List<Order> findAllOrders();

    @Select("select * from `admin` where username = #{username}")
    Admin findByUsername(String username);

    // 获取用户总数
    @Select("SELECT COUNT(*) FROM user")
    int getUserCount();

    // 获取商品总数
    @Select("SELECT COUNT(*) FROM product")
    int getProductCount();

    // 获取订单总数
    @Select("SELECT COUNT(*) FROM orders")
    int getOrderCount();

    // 获取总销售额
    @Select("SELECT SUM(price) FROM orders")
    Double getTotalSales();

    // 获取周销售数据
    @Select("SELECT DAYOFWEEK(createTime) as day, SUM(price) as total " +
            "FROM orders " +
            "WHERE createTime >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
            "GROUP BY DAYOFWEEK(createTime)")
    @MapKey("day")
    Map<Integer, Map<String, Object>> getWeeklySalesData();

    // 获取分类名称（按categoryId排序）
    @Select("SELECT categoryName FROM category ORDER BY categoryId")
    List<String> getCategoryNames();

    // 获取所有分类的销售数据（无数据的分类total为NULL）
    @Select("SELECT c.categoryId, c.categoryName, IFNULL(SUM(o.price), 0) as total " +
            "FROM category c " +
            "LEFT JOIN product p ON c.categoryId = p.categoryId " +
            "LEFT JOIN orders o ON p.productId = o.productId " +
            "GROUP BY c.categoryId, c.categoryName " +
            "ORDER BY c.categoryId")
    List<Map<String, Object>> getCategorySalesDataWithZero();

    /**
     * 获取当月每天的销售数据
     */
    @Select("SELECT DAY(createTime) as day, SUM(price) as sales " +
            "FROM orders " +
            "WHERE MONTH(createTime) = MONTH(CURRENT_DATE()) " +
            "AND YEAR(createTime) = YEAR(CURRENT_DATE()) " +
            "GROUP BY DAY(createTime) " +
            "ORDER BY day")
    List<Map<String, Object>> getMonthlySales();

    // 获取最近活动
    @Select("SELECT a.*, u.username FROM (" +
            "SELECT 'ORDER_CREATE' AS activityType, buyerId AS user_id, " +
            "CONCAT('创建订单 #', orderNumber) AS description, createTime AS createdAt " +
            "FROM orders " +
            "UNION ALL " +
            "SELECT 'USER_REGISTER' AS activityType, user_id, '新用户注册' AS description, register_time AS createdAt " +
            "FROM user " +
            "UNION ALL " +
            "SELECT 'PRODUCT_ADD' AS activityType, userId, CONCAT('上架商品: ', title) AS description, createTime AS createdAt " +
            "FROM product" +
            ") a LEFT JOIN user u ON a.user_id = u.user_id " +
            "ORDER BY a.createdAt DESC LIMIT #{count}")
    List<AdminActivityVO> getRecentActivities(@Param("count") int count);

    // 获取今日新增用户数
    @Select("SELECT COUNT(*) FROM user WHERE DATE(register_time) = CURDATE()")
    int getTodayNewUserCount();

    // 获取昨日新增用户数
    @Select("SELECT COUNT(*) FROM user WHERE DATE(register_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)")
    int getYesterdayNewUserCount();

    @Select("SELECT COUNT(*) FROM product WHERE DATE(createTime) = CURDATE()")
    int getTodayNewProductCount();

    @Select("SELECT COUNT(*) FROM product WHERE DATE(createTime) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)")
    int getYesterdayNewProductCount();

    // 获取今日订单数
    @Select("SELECT COUNT(*) FROM orders WHERE DATE(createTime) = CURDATE()")
    int getTodayOrderCount();

    // 获取昨日订单数（保留原方法）
    @Select("SELECT COUNT(*) FROM orders WHERE DATE(createTime) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)")
    int getYesterdayOrderCount();

    // 获取今日销售额
    @Select("SELECT SUM(price) FROM orders WHERE DATE(createTime) = CURDATE()")
    Double getTodaySales();

    // 获取昨日销售额（保留原方法）
    @Select("SELECT SUM(price) FROM orders WHERE DATE(createTime) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)")
    Double getYesterdaySales();

    @Select("SELECT * FROM user WHERE " +
            "username LIKE CONCAT('%', #{keyword}, '%') OR " +
            "student_id LIKE CONCAT('%', #{keyword}, '%') OR " +
            "phone LIKE CONCAT('%', #{keyword}, '%') " +
            "LIMIT #{offset}, #{limit}")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "realName", column = "real_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "registerTime", column = "register_time")
    })
    List<User> searchUsersWithPagination(@Param("keyword") String keyword,
                                         @Param("offset") int offset,
                                         @Param("limit") int limit);

    @Select("SELECT COUNT(*) FROM user WHERE " +
            "username LIKE CONCAT('%', #{keyword}, '%') OR " +
            "student_id LIKE CONCAT('%', #{keyword}, '%') OR " +
            "phone LIKE CONCAT('%', #{keyword}, '%')")
    int countUsersByKeyword(@Param("keyword") String keyword);

    @Select("SELECT * FROM user LIMIT #{offset}, #{limit}")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "realName", column = "real_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "registerTime", column = "register_time"),
            @Result(property = "lastLoginTime", column = "last_login_time")
    })
    List<User> findAllUsersWithPagination(@Param("offset") int offset,
                                          @Param("limit") int limit);

    @Select("SELECT COUNT(*) FROM user")
    int countAllUsers();

    @Select("<script>" +
            "SELECT * FROM product " +
            "<where>" +
            "   <if test='keyword != null'>" +
            "       AND (title LIKE #{keyword} OR description LIKE #{keyword})" +
            "   </if>" +
            "   <if test='categoryId != null'>" +
            "       AND categoryId = #{categoryId}" +
            "   </if>" +
            "   <if test='status != null'>" +
            "       AND status = #{status}" +
            "   </if>" +
            "</where>" +
            "<if test='orderBy != null'>" +
            "   ORDER BY ${orderBy}" +
            "</if>" +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<Product> searchProductsWithPagination(Map<String, Object> params);

    @Select("<script>" +
            "SELECT COUNT(*) FROM product " +
            "<where>" +
            "   <if test='keyword != null'>" +
            "       AND (title LIKE #{keyword} OR description LIKE #{keyword})" +
            "   </if>" +
            "   <if test='categoryId != null'>" +
            "       AND categoryId = #{categoryId}" +
            "   </if>" +
            "   <if test='status != null'>" +
            "       AND status = #{status}" +
            "   </if>" +
            "</where>" +
            "</script>")
    int countProducts(Map<String, Object> params);

    @Select("<script>" +
            "SELECT o.* FROM orders o " +
            "LEFT JOIN product p ON o.productId = p.productId " +
            "LEFT JOIN user buyer ON o.buyerId = buyer.user_id " +
            "LEFT JOIN user seller ON o.sellerId = seller.user_id " +
            "<where>" +
            "   <if test='keyword != null'>" +
            "       AND (" +
            "           o.orderNumber LIKE #{keyword} OR " +
            "           p.title LIKE #{keyword} OR " +
            "           buyer.username LIKE #{keyword} OR " +
            "           seller.username LIKE #{keyword}" +
            "       )" +
            "   </if>" +
            "   <if test='status != null'>" +
            "       AND o.status = #{status}" +
            "   </if>" +
            "   <if test='timeRange != null'>" +
            "       <choose>" +
            "           <when test='timeRange == \"today\"'>" +
            "               AND DATE(o.createTime) = CURDATE()" +
            "           </when>" +
            "           <when test='timeRange == \"week\"'>" +
            "               AND o.createTime >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)" +
            "           </when>" +
            "           <when test='timeRange == \"month\"'>" +
            "               AND o.createTime >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)" +
            "           </when>" +
            "       </choose>" +
            "   </if>" +
            "</where>" +
            "<if test='orderBy != null'>" +
            "   ORDER BY ${orderBy}" +
            "</if>" +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    @Results({
            @Result(property = "orderId", column = "orderId"),
            @Result(property = "orderNumber", column = "orderNumber"),
            @Result(property = "productId", column = "productId"),
            @Result(property = "buyerId", column = "buyerId"),
            @Result(property = "sellerId", column = "sellerId"),
            @Result(property = "price", column = "price"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "createTime")
    })
    List<Order> searchOrdersWithPagination(Map<String, Object> params);

    @Select("<script>" +
            "SELECT COUNT(*) FROM orders o " +
            "LEFT JOIN product p ON o.productId = p.productId " +
            "LEFT JOIN user buyer ON o.buyerId = buyer.user_id " +
            "LEFT JOIN user seller ON o.sellerId = seller.user_id " +
            "<where>" +
            "   <if test='keyword != null'>" +
            "       AND (" +
            "           o.orderNumber LIKE #{keyword} OR " +
            "           p.title LIKE #{keyword} OR " +
            "           buyer.username LIKE #{keyword} OR " +
            "           seller.username LIKE #{keyword}" +
            "       )" +
            "   </if>" +
            "   <if test='status != null'>" +
            "       AND o.status = #{status}" +
            "   </if>" +
            "   <if test='timeRange != null'>" +
            "       <choose>" +
            "           <when test='timeRange == \"today\"'>" +
            "               AND DATE(o.createTime) = CURDATE()" +
            "           </when>" +
            "           <when test='timeRange == \"week\"'>" +
            "               AND o.createTime >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)" +
            "           </when>" +
            "           <when test='timeRange == \"month\"'>" +
            "               AND o.createTime >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)" +
            "           </when>" +
            "       </choose>" +
            "   </if>" +
            "</where>" +
            "</script>")
    int countOrders(Map<String, Object> params);

    @Delete("DELETE FROM orders where orderId = #{orderId}")
    int deleteOrders(Integer orderId);
}
